package com.pengtu.gsj.dao.system;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import com.pengtu.gsj.dao.hibernate.HibernateDao;
import com.pengtu.gsj.entity.CheckMenusVo;
import com.pengtu.gsj.entity.CheckResultVo;
import com.pengtu.gsj.entity.CheckTableMenusVo;
import com.pengtu.gsj.entity.app.Sysmenu;

/** 
 * @author zl 
 * @version 创建时间：2017年4月5日 上午10:57:36 
 * 类说明 
 */
@Repository
public class SystemMenuDao extends HibernateDao<Sysmenu, String>{

	public List<Sysmenu> findByUserId(String id) {
		String hql = "select distinct m from Sysmenu m, Role r, User u " +
				" where m in elements (r.menuList) and r in elements (u.roleList)" +
				" and m.delFlag='0' and m.isShow ='1' and r.delFlag='0' and u.delFlag='0' and u.id=?" + 
				" order by m.sort";
			return this.find(hql,id);
	}

	public Sysmenu findTopMenu() {
		String hql = "from Sysmenu m where m.parentIds='0' and m.delFlag='0'";
		return this.findUnique(hql);
	}

	public List<Sysmenu> findByParentIds(String id) {
		String hql = "from Sysmenu m where m.parentIds like ? and m.delFlag = '0'";
		return this.find(hql, "%"+id+"%");
	}
	
	public void delete(String id){
		String hql = "update Sysmenu set delFlag='1' where id = ? or parentIds like ?";
		this.batchExecute(hql,id,"%"+id+"%");
	}
	
	public List<Sysmenu> getSysmenusByParentId(String parentId) {
		String hql = "from Sysmenu m where m.parent.id like ? and m.delFlag = '0' order by m.sort asc";
		return this.find(hql, "%" + parentId + "%");
	}

	public String getMaxParentIds(String parentId) {
		String sql = "select  top 1 t.PARENTIDS from T_APP_SYSMENU t WHERE t.PARENTIDS LIKE '%"
				+ parentId
				+ "%' and t.delFlag = '0' order by LEN(t.PARENTIDS) desc";
		return (String) this.getSession().createSQLQuery(sql).list().get(0);
	}

	public String getParentIds(String id) {
		String sql = "select  t.PARENTIDS from T_APP_SYSMENU t where id = '"
				+ id + "' and t.delFlag = '0'";
		return (String) this.getSession().createSQLQuery(sql).list().get(0);
	}
	
	public Sysmenu findById(String id) {
		String hql = "from Sysmenu m where m.id = '"+id+"' and m.delFlag='0'";
		return this.findUnique(hql);
	}
	
	@SuppressWarnings("unchecked")
	public List<String> getIdStrByParentId(String id) {
		String hql = "select m.id from Sysmenu m where m.parent.id like ? and m.delFlag = '0' order by m.sort asc";
		Query query = getSession().createQuery(hql);
		query.setString(0, id);
		return query.list();
	};
	
	@SuppressWarnings("unchecked")
	public List<String> getMenuByParentIds(String id) {
		String hql = "select m.id from Sysmenu m where m.parentIds like ? and m.system = '1' and m.menuType= '0' and m.delFlag = '0' and m.checkHref != '' order by m.createDate asc";
		Query query = getSession().createQuery(hql);
		query.setString(0, "%"+id+"%");
		return query.list();
	};
	
	public List<Sysmenu> getSysmenus(String id){
		String hql = "select  m.id from Sysmenu m where m.parent.id =?";
		return this.find(hql, id);
	}
	
	@SuppressWarnings("unchecked")
	public List<Sysmenu> getCheckModule(String projectId){
		String sql = "select *  FROM T_APP_SYSMENU t, R_APP_CHECKMENU s where s.MENUID = t.ID and t.PARENTID ='402880f65b8f1cea015b8f24ae3d0001' and s.CHECKID =?";
		Query query = getSession().createSQLQuery(sql).addEntity(Sysmenu.class);
		query.setString(0, projectId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<CheckResultVo> getCheckResultVoByParentId(String projectId, String parentId){
		String sql = "SELECT sum(t.tableFull) as tableFull,sum(t.testFull) as testFull,SUM(t.testFull)/SUM(t.tableFull) as perfection FROM T_CK_CHECKTABLE t where t.projId =? and t.menuId  IN (SELECT s.ID from T_APP_SYSMENU s, R_APP_CHECKMENU k where s.ID = k.MENUID and s.PARENTIDS LIKE ? and s.SYSTEM ='1' and s.MENUTYPE ='0' and s.DELFLAG='0' and k.CHECKID =?)";
		Query query = getSession().createSQLQuery(sql)
				.addScalar("tableFull", StandardBasicTypes.DOUBLE)
				.addScalar("testFull", StandardBasicTypes.DOUBLE)
				.addScalar("perfection", StandardBasicTypes.DOUBLE)
				.setResultTransformer(Transformers.aliasToBean(CheckResultVo.class));
		query.setString(0, projectId);
		query.setString(1, "%"+parentId+"%");
		query.setString(2, projectId);
		return query.list();
	}
		
	public List<Sysmenu> getAllSysmenuList(String[] ids){
		StringBuilder b = new StringBuilder("from Sysmenu m where m.delFlag = '0' and m.id in (");
		for(String id : ids){
		  b.append("'"+id+"',");
		}
		b = b.deleteCharAt(b.length()-1); // 去掉最后一个,
		b.append(")");
		 
		String hql = b.toString();
		return this.find(hql);
	}
	
	@SuppressWarnings("unchecked")
	public List<CheckMenusVo> getMenuList(String menuId, String projId){
		String sql = "SELECT s.ID,s.NAME,s.parentId from T_APP_SYSMENU s, R_APP_CHECKMENU k where s.ID = k.MENUID "+
				"and s.PARENTIDS LIKE ? and s.SYSTEM ='1' and s.MENUTYPE ='0' and s.DELFLAG='0' and k.CHECKID =?";
		Query query = getSession().createSQLQuery(sql)
				.addScalar("id", StandardBasicTypes.STRING)
				.addScalar("name", StandardBasicTypes.STRING)
				.addScalar("parentId", StandardBasicTypes.STRING)
				.setResultTransformer(Transformers.aliasToBean(CheckMenusVo.class));
		query.setString(0, "%"+menuId+"%");
		query.setString(1, projId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<CheckTableMenusVo> getTableMenuList(String menuId, String projId){
		String sql = "SELECT s.ID,s.name,s.PARENTID,t.tableFull,t.testFull,t.perfection from T_APP_SYSMENU s, R_APP_CHECKMENU k, T_CK_CHECKTABLE t where "+
					 "s.ID = k.MENUID and s.ID = t.menuId and s.PARENTIDS LIKE ? and s.SYSTEM ='1' and s.MENUTYPE ='0' and s.DELFLAG='0'"+
					 "and k.CHECKID =? AND t.projId=? AND t.DELFLAG='0' ORDER BY t.menuId";
		Query query = getSession().createSQLQuery(sql)
				.addScalar("id", StandardBasicTypes.STRING)
				.addScalar("name", StandardBasicTypes.STRING)
				.addScalar("parentId", StandardBasicTypes.STRING)
				.addScalar("tableFull", StandardBasicTypes.DOUBLE)
				.addScalar("testFull", StandardBasicTypes.DOUBLE)
				.addScalar("perfection", StandardBasicTypes.DOUBLE)
				.setResultTransformer(Transformers.aliasToBean(CheckTableMenusVo.class));
		query.setString(0, "%"+menuId+"%");
		query.setString(1, projId);
		query.setString(2, projId);
		return query.list();
	}
}
